import { sqlite3Client } from "../dist/index.cjs"

let sql = `
CREATE TABLE IF NOT EXISTS base_config (
  key STRING NOT NULL DEFAULT '', 
  val TEXT NOT NULL DEFAULT '', 
  PRIMARY KEY (key)
);
`
// new 一个对象
let db = new sqlite3Client()

const test = async () => {

	// 打开一个数据库, 返回 boolean
	db.open({ "dbName": "./a.db" })
	// 申请一个worker 
	await db.worker(async (w) => {
		// 事务模式执行一条语句或多条, 同步
		// sql: string|string[],返回 boolean
		w.transactionExec(sql)
		// 事务内执行, 同步
		w.transaction(() => {
			// 申请一个"预处理"
			let stmt = w.stmt()
			// 编译预处理, 返回 boolean
			stmt.prepare("INSERT OR IGNORE INTO base_config(key, val)values(:key, :val);")
			for (let i = 100; i < 1000; i++) {
				// 绑定, 返回 boolean
				stmt.bind({ key: "key_" + i, val: "val_" + i })
				// 执行, 返回 boolean
				stmt.run()
			}
		})
		w.transaction(() => {
			let stmt = w.stmt()
			stmt.prepare("UPDATE base_config SET val=:val WHERE key LIKE '%'||:valAAA||'%'")
			stmt.bind({ valAAA: 555 })
			stmt.run()
		})

		//
		let stmt = w.stmt()
		stmt.prepare("SELECT * FROM base_config LIMIT 1,2")
		stmt.stepByStep((row) => {
			console.log("AAA", row)
		})

		//
		stmt.prepare("SELECT * FROM base_config WHERE key LIKE '%'||:keyPPP||'%' LIMIT 3")
		stmt.bind({ keyPPP: "key_99" })
		stmt.stepByStep((row) => {
			console.log("BBB", row)
		})

		//
		stmt.prepare("SELECT * FROM base_config WHERE key LIKE '%'||?||'%' LIMIT 3")
		stmt.bind(["key_99"])
		stmt.stepByStep((row) => {
			console.log("CCC", row)
		})

		//
		stmt.prepare("SELECT * FROM base_config WHERE key LIKE '%'||?||'%' LIMIT 3")
		stmt.bind(["key_99"])
		// 单步执行
		while (1) {
			let r = stmt.step()
			if (!r.status) {
				console.error("db error")
				break;
			}
			let row = r.row
			if (!row) {
				break
			}
			console.log("DDD", row)
		}

		//
		stmt.prepare("SELECT * FROM base_config WHERE key LIKE '%'||?||'%' LIMIT 3")
		stmt.bind(["key_99"])
		let rows = stmt.runAndGetAllRows();
		if (!rows.status) {
			console.error("db error")
		} else {
			console.log("EEE", rows.rows)
		}
	})
}

test()